﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using BCMS.DAL.IDAL;
using BCMS.Business.DataContract;

namespace BCMS.DAL.MySQL
{
    public class Users : IUser
    {
        private MySqlConnection _internalConnection;
        private MySqlTransaction _internalADOTransaction = null;

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new MySqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        #region  Params
        private const string PARM_ID = "@ID";
        private const string PARM_USERNAME = "@USERNAME";
        private const string PARM_PASSWORD = "@PASSWORD";
        private const string PARM_ROLES = "@ROLES";
        private const string PARM_FULLNAME = "@FULLNAME";
        private const string PARM_EMAIL = "@EMAIL";
        private const string PARM_PHONE = "@PHONE";
        private const string PARM_ADRESS = "@ADDRESS";
        private const string PARM_POSITION = "@POSITION";
        private const string PARM_DEPARTMENT = "@DEPARTMENT";
        private const string PARM_STATUS = "@STATUS";
        #endregion

        #region SQL string
        private const string USER_INSERT = "INSERT INTO users (USERNAME, PASSWORD, ROLES, FULLNAME, EMAIL, PHONE, ADDRESS, POSITION, DEPARTMENT) VALUES (@USERNAME, MD5(@PASSWORD), @ROLES, @FULLNAME, @EMAIL, @PHONE, @ADDRESS, @POSITION, @DEPARTMENT); SELECT LAST_INSERT_ID();";

        private const string GET_BYNAME = "SELECT * FROM users WHERE username=@username";

        private const string GET_BYID = "SELECT * FROM users WHERE Id=@Id";

        private const string USER_GET_ALL = "SELECT * FROM users";

        private const string USER_GET_ROLES_BY_ID = "SELECT roles FROM users WHERE ID = @ID";
        private const string USER_LOGIN = "SELECT * FROM users WHERE LOWER(USERNAME) = LOWER(@USERNAME) AND PASSWORD = MD5(@PASSWORD)";
        private const string USER_CHANGE_PASS = "UPDATE users SET PASSWORD = MD5(@PASSWORD) WHERE ID = @ID";
        private const string USER_CHECK_EXISTED = "SELECT COUNT(ID) FROM users WHERE LOWER(USERNAME) = LOWER(@USERNAME)";
        private const string USER_UPDATE_ROLES = "UPDATE users SET ROLES = @ROLES WHERE ID=@ID";
        private const string USER_UPDATE_INFO = "UPDATE users SET FULLNAME=@FULLNAME, EMAIL=@EMAIL, PHONE=@PHONE, ADDRESS=@ADDRESS, POSITION=@POSITION, DEPARTMENT=@DEPARTMENT WHERE ID=@ID";
        #endregion

        #region IUser Members

        public void updateUserInfo(int userId, string fullname, string email, string phone, string address, string position, string department)
        {
            try
            {
                MySqlParameter[] _params = getUserUpdateInfoParameters();
                _params[0].Value = userId;
                _params[1].Value = fullname;
                _params[2].Value = email;
                _params[3].Value = phone;
                _params[4].Value = address;
                _params[5].Value = position;
                _params[6].Value = department;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, USER_UPDATE_INFO, _params);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }

        public List<UserInfo> getUsers(int page, int offset)
        {
            throw new NotImplementedException();
        }

        public UserInfo getUserById(int uID)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parm = new MySqlParameter("@Id", MySqlDbType.Int32);
                parm.Value = uID;

                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BYID, parm);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return ConvertToObject(rdr);
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        public int insertUser(string username, string password, string roles, string fullname, string email, string phone, string address, string position, string department)
        {
            try
            {
                MySqlParameter[] _params = getInsertUserParameters();
                _params[0].Value = username;
                _params[1].Value = password;
                _params[2].Value = roles;
                _params[3].Value = fullname;
                _params[4].Value = email;
                _params[5].Value = phone;
                _params[6].Value = address;
                _params[7].Value = position;
                _params[8].Value = department;

                return MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, USER_INSERT, _params);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private MySqlParameter[] getUserCheckExistedParameters()
        {
            MySqlParameter[] _params = MyHelper.GetCacheParameters(USER_CHECK_EXISTED);

            if (_params == null)
            {
                _params = new MySqlParameter[]{
                            new MySqlParameter(PARM_USERNAME, MySqlDbType.VarChar,30)
                };

                MyHelper.CacheParameters(USER_CHECK_EXISTED, _params);
            }
            return _params;
        }

        public UserInfo getUserByName(string userName)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parm = new MySqlParameter("@username", MySqlDbType.VarChar);
                parm.Value = userName;

                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BYNAME, parm);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return ConvertToObject(rdr);
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        private UserInfo ConvertToObject(MySqlDataReader rdr)
        {
            UserInfo item = new UserInfo();
            item.ID = Convert.ToInt32(rdr["ID"]);
            item.UserName = rdr["USERNAME"].ToString();
            item.Password = string.Empty;
            item.Roles = rdr["ROLES"].ToString();
            item.FullName = rdr["FULLNAME"].ToString();
            item.Email = rdr["EMAIL"].ToString();
            item.Phone = rdr["PHONE"].ToString();
            item.Address = rdr["ADDRESS"].ToString();
            item.Position = rdr["POSITION"].ToString();
            item.Department = rdr["DEPARTMENT"].ToString();

            return item;
        }

        private MySqlParameter[] getUserUpdateInfoParameters()
        {
            MySqlParameter[] _params = MyHelper.GetCacheParameters(USER_UPDATE_INFO);

            if (_params == null)
            {
                _params = new MySqlParameter[]{
                            new MySqlParameter(PARM_ID, MySqlDbType.Int32),
                            new MySqlParameter(PARM_FULLNAME, MySqlDbType.VarChar,30),
                            new MySqlParameter(PARM_EMAIL, MySqlDbType.VarChar,50),
                            new MySqlParameter(PARM_PHONE, MySqlDbType.VarChar,20),
                            new MySqlParameter(PARM_ADRESS, MySqlDbType.VarChar, 100),
                            new MySqlParameter(PARM_POSITION, MySqlDbType.VarChar, 100),
                            new MySqlParameter(PARM_DEPARTMENT, MySqlDbType.VarChar, 100)};
            }
            return _params;
        }

        private MySqlParameter[] getInsertUserParameters()
        {
            MySqlParameter[] _params = MyHelper.GetCacheParameters(USER_INSERT);

            if (_params == null)
            {
                _params = new MySqlParameter[]{
                            new MySqlParameter(PARM_USERNAME, MySqlDbType.VarChar,30),
                            new MySqlParameter(PARM_PASSWORD, MySqlDbType.VarChar,100),
                            new MySqlParameter(PARM_ROLES, MySqlDbType.VarChar,50),
                            new MySqlParameter(PARM_FULLNAME, MySqlDbType.VarChar,50),
                            new MySqlParameter(PARM_EMAIL, MySqlDbType.VarChar,50),
                            new MySqlParameter(PARM_PHONE, MySqlDbType.VarChar,20),
                            new MySqlParameter(PARM_ADRESS, MySqlDbType.VarChar, 100),
                            new MySqlParameter(PARM_POSITION, MySqlDbType.VarChar, 100),
                            new MySqlParameter(PARM_DEPARTMENT, MySqlDbType.VarChar, 100)               
                };

                MyHelper.CacheParameters(USER_INSERT, _params);
            }
            return _params;
        }

        public UserInfo getCouponByUserName(string username)
        {
            throw new NotImplementedException();
        }

        public void deleteUserByUserName(string serial)
        {
            throw new NotImplementedException();
        }

        public void deleteUserByUserID(string serial)
        {
            throw new NotImplementedException();
        }

        public void changePassword(int id, string newPassword)
        {
            MySqlParameter[] _params = getUserChangePasswordParameters();
            _params[0].Value = id;
            _params[1].Value = newPassword;
            MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, USER_CHANGE_PASS, _params);
        }

        public void updateUserRoles(int uID, string roles)
        {
            MySqlParameter[] _params = new MySqlParameter[] { 
                new MySqlParameter(PARM_ID, MySqlDbType.Int32),
                new MySqlParameter(PARM_ROLES, MySqlDbType.VarChar,30)
            };
            _params[0].Value = uID;
            _params[1].Value = roles;
            MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction,
                CommandType.Text, USER_UPDATE_ROLES, _params);
        }

        private MySqlParameter[] getUserChangePasswordParameters()
        {
            MySqlParameter[] _params = MyHelper.GetCacheParameters(USER_CHANGE_PASS);

            if (_params == null)
            {
                _params = new MySqlParameter[]{
                            new MySqlParameter(PARM_ID, MySqlDbType.Int32),
                            new MySqlParameter(PARM_PASSWORD, MySqlDbType.VarChar,30)
                };

                MyHelper.CacheParameters(USER_CHANGE_PASS, _params);
            }
            return _params;
        }

        public UserInfo login(string username, string password)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_USERNAME, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_PASSWORD, MySqlDbType.VarChar)
                };
                parms[0].Value = username;
                parms[1].Value = password;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, USER_LOGIN, parms);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return ConvertToObject(rdr);
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }

        }

        public List<UserInfo> getAllUsers()
        {
            List<UserInfo> list = new List<UserInfo>();
            MySql.Data.MySqlClient.MySqlDataReader reader = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, USER_GET_ALL, null);
            while (reader.Read())
            {
                list.Add(new UserInfo(
                    int.Parse(reader["ID"].ToString()),
                    reader["UserName"].ToString(),
                    "",
                    reader["Roles"].ToString(),
                    reader["FullName"].ToString(),
                    reader["Email"].ToString(),
                    reader["Phone"].ToString(),
                    reader["Address"].ToString(),
                    reader["Position"].ToString(),
                    reader["Department"].ToString()));
            }
            return list;
        }

        private MySqlParameter[] getUserLoginParameters()
        {
            MySqlParameter[] _params = MyHelper.GetCacheParameters(USER_LOGIN);

            if (_params == null)
            {
                _params = new MySqlParameter[]{
                            new MySqlParameter(PARM_USERNAME, MySqlDbType.VarChar,30),
                            new MySqlParameter(PARM_PASSWORD, MySqlDbType.VarChar,20)
                };

                MyHelper.CacheParameters(USER_LOGIN, _params);
            }
            return _params;
        }
        #endregion
    }
}
